﻿#region Copyright (C) Sector7 Software Studio. All rights reserved
/*******************************************************************************************
 * Creation
 *      Author:         Wolf Lai
 *      Date:           2013/6/6 星期四 23:37:46
 *      Version:        
 * Modification 
 *      Author: 
 *      Date: 
 *      Description: 
 *      Version:
 *******************************************************************************************/
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Sector7.Medical.EntityType;
using Sector7.Framework.DB;
using Sector7.Medical.Consts;
using Sector7.Medical.DAO.Utility;
using Sector7.Framework.EntityType;

namespace Sector7.Medical.DAO.Standard
{
    /// <summary>
    /// 员工数据访问对象
    /// </summary>
    public class EmployeeDAO : DAOBase<M7_Employee>, IEmployeeDAO
    {
        #region Public functions

        public override M7_Employee Read(object primaryKey)
        {
            M7_Employee employee = null;
            string sql = @"
                SELECT
                    *
                FROM
                    S7_Citizen c
                    LEFT JOIN M7_Employee e ON c.CitizenId = e.CitizenId
                WHERE
                    c.CitizenId = @CitizenId";

            using (DBHelper helper = new DBHelper())
            {
                var reader = helper.ExecuteQuery(sql,
                    DBUtil.CreateSqlParameter(primaryKey, C_ColumnName.CITIZEN_ID));

                if (reader.Read())
                {
                    employee = new M7_Employee();
                    DBUtil.ReadData(reader, employee);
                }
            }

            return employee;
        }

        public string GetUniqueId(string username, string password)
        {
            string citizenId = string.Empty;
            string sql = @"
                SELECT
                    c.CitizenId
                FROM
                    S7_Citizen c
                    LEFT JOIN M7_Employee e ON c.CitizenId = e.CitizenId
                WHERE
                    c.Username = @Username AND e.Password = @Password";

            using (DBHelper helper = new DBHelper())
            {
                SqlDataReader reader = helper.ExecuteQuery(sql,
                    DBUtil.CreateSqlParameter(username, C_ColumnName.USERNAME),
                    DBUtil.CreateSqlParameter(password, C_ColumnName.PASSWORD));

                if (reader.Read())
                {
                    citizenId = reader[C_ColumnName.CITIZEN_ID].ToString();
                }
            }

            return citizenId;
        }

        /// <summary>
        /// 获取所有员工
        /// </summary>
        /// <returns></returns>
        public List<M7_Employee> ReadAll()
        {
            string sql = @"
                SELECT *
                    FROM M7_Employee emp, S7_Citizen citi
                    WHERE 1 = 1
                    and emp.CitizenId = citi.CitizenId";

            using (DBHelper helper = new DBHelper())
            {
                List<M7_Employee> employeeList = new List<M7_Employee>();

                SqlDataReader reader = helper.ExecuteQuery(sql);
                while (reader.Read())
                {
                    object employee = new M7_Employee();
                    DBUtil.ReadData(reader, employee);

                    employeeList.Add(employee as M7_Employee);
                }

                return employeeList;
            }
        }

        /// <summary>
        /// 获取所有医生
        /// </summary>
        /// <returns></returns>
        public List<M7_Employee> ReadAllByDoctorOrSpecialist()
        {
            string sql = @"
                SELECT *
                    FROM M7_Employee emp, S7_Citizen citi
                    WHERE 1 = 1
                    and emp.CitizenId = citi.CitizenId
                    and (emp.IsDoctor = 1 or emp.IsSpecialist = 1)";

            using (DBHelper helper = new DBHelper())
            {
                List<M7_Employee> employeeList = new List<M7_Employee>();

                SqlDataReader reader = helper.ExecuteQuery(sql);
                while (reader.Read())
                {
                    object employee = new M7_Employee();
                    DBUtil.ReadData(reader, employee);

                    employeeList.Add(employee as M7_Employee);
                }

                return employeeList;
            }
        }

        /// <summary>
        /// 添加员工
        /// </summary>
        /// <param name="employee">员工信息</param>
        /// <returns></returns>
        public override object Create(M7_Employee employee)
        {
            string sql = @"
                INSERT INTO M7_Employee
                        (EmpId
                        ,CitizenId
                        ,DeptId
                        ,AreaId
                        ,EmpGroupId
                        ,DutyId
                        ,CompetenceId
                        ,Remarks
                        ,IsOnDuty
                        ,IsSpecialist
                        ,IsAnesthesia
                        ,IsDoctor
                        ,IsNurse
                        ,StorageId
                        ,PharmacyId
                        ,ChargeDeptId
                        ,Password
                        ,HireDate
                        ,LeaveDate)
                    VALUES
                        (@EmpId
                        ,@CitizenId
                        ,@DeptId
                        ,@AreaId
                        ,@EmpGroupId
                        ,@DutyId
                        ,@CompetenceId
                        ,@Remarks
                        ,@IsOnDuty
                        ,@IsSpecialist
                        ,@IsAnesthesia
                        ,@IsDoctor
                        ,@IsNurse
                        ,@StorageId
                        ,@PharmacyId
                        ,@ChargeDeptId
                        ,@Password
                        ,@HireDate
                        ,@LeaveDate)";

            using (DBHelper helper = new DBHelper())
            {
                return helper.ExecuteNonQuery(sql,
                    DBUtil.CreateSqlParameter(employee.EmpId, "EmpId"),
                    DBUtil.CreateSqlParameter(employee.CitizenId, "CitizenId"),
                    DBUtil.CreateSqlParameter(employee.DeptId, "DeptId"),
                    DBUtil.CreateSqlParameter(employee.AreaId, "AreaId"),
                    DBUtil.CreateSqlParameter(employee.EmpGroupId, "EmpGroupId"),
                    DBUtil.CreateSqlParameter(employee.DutyId, "DutyId"),
                    DBUtil.CreateSqlParameter(employee.CompetenceId, "CompetenceId"),
                    DBUtil.CreateSqlParameter(employee.Remarks, "Remarks"),
                    DBUtil.CreateSqlParameter(employee.IsOnDuty, "IsOnDuty"),
                    DBUtil.CreateSqlParameter(employee.IsSpecialist, "IsSpecialist"),
                    DBUtil.CreateSqlParameter(employee.IsAnesthesia, "IsAnesthesia"),
                    DBUtil.CreateSqlParameter(employee.IsDoctor, "IsDoctor"),
                    DBUtil.CreateSqlParameter(employee.IsNurse, "IsNurse"),
                    DBUtil.CreateSqlParameter(employee.StorageId, "StorageId"),
                    DBUtil.CreateSqlParameter(employee.PharmacyId, "PharmacyId"),
                    DBUtil.CreateSqlParameter(employee.ChargeDeptId, "ChargeDeptId"),
                    DBUtil.CreateSqlParameter(employee.Password, "Password"),
                    DBUtil.CreateSqlParameter(employee.HireDate, "HireDate"),
                    DBUtil.CreateSqlParameter(employee.LeaveDate, "LeaveDate"));
            }
        }

        /// <summary>
        /// 更新员工
        /// </summary>
        /// <param name="employee">员工信息</param>
        /// <returns></returns>
        public int Update(M7_Employee employee)
        {
            string sql = @"
                UPDATE M7_Employee Set
                        CitizenId = @CitizenId
                        ,DeptId = @DeptId
                        ,AreaId = @AreaId
                        ,EmpGroupId = @EmpGroupId
                        ,DutyId = @DutyId
                        ,CompetenceId = @CompetenceId
                        ,Remarks = @Remarks
                        ,IsOnDuty = @IsOnDuty
                        ,IsSpecialist = @IsSpecialist
                        ,IsAnesthesia = @IsAnesthesia
                        ,IsDoctor = @IsDoctor
                        ,IsNurse = @IsNurse
                        ,StorageId = @StorageId
                        ,PharmacyId = @PharmacyId
                        ,ChargeDeptId = @ChargeDeptId
                        ,Password = @Password
                        ,HireDate = @HireDate
                        ,LeaveDate = @LeaveDate
                    WHERE EmpId = @EmpId";

            using (DBHelper helper = new DBHelper())
            {
                return helper.ExecuteNonQuery(sql,
                    DBUtil.CreateSqlParameter(employee.CitizenId, "CitizenId"),
                    DBUtil.CreateSqlParameter(employee.DeptId, "DeptId"),
                    DBUtil.CreateSqlParameter(employee.AreaId, "AreaId"),
                    DBUtil.CreateSqlParameter(employee.EmpGroupId, "EmpGroupId"),
                    DBUtil.CreateSqlParameter(employee.DutyId, "DutyId"),
                    DBUtil.CreateSqlParameter(employee.CompetenceId, "CompetenceId"),
                    DBUtil.CreateSqlParameter(employee.Remarks, "Remarks"),
                    DBUtil.CreateSqlParameter(employee.IsOnDuty, "IsOnDuty"),
                    DBUtil.CreateSqlParameter(employee.IsSpecialist, "IsSpecialist"),
                    DBUtil.CreateSqlParameter(employee.IsAnesthesia, "IsAnesthesia"),
                    DBUtil.CreateSqlParameter(employee.IsDoctor, "IsDoctor"),
                    DBUtil.CreateSqlParameter(employee.IsNurse, "IsNurse"),
                    DBUtil.CreateSqlParameter(employee.StorageId, "StorageId"),
                    DBUtil.CreateSqlParameter(employee.PharmacyId, "PharmacyId"),
                    DBUtil.CreateSqlParameter(employee.ChargeDeptId, "ChargeDeptId"),
                    DBUtil.CreateSqlParameter(employee.Password, "Password"),
                    DBUtil.CreateSqlParameter(employee.HireDate, "HireDate"),
                    DBUtil.CreateSqlParameter(employee.LeaveDate, "LeaveDate"),
                    DBUtil.CreateSqlParameter(employee.EmpId, "EmpId"));
            }
        }
        #endregion

        #region Structure
        public EmployeeDAO()
        {
            m_tableName = "M7_EmployeeGroup";
            m_primaryKeyName = "EmpGroupId";
        }
        #endregion
    }
}
